
package com.common.database.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.springframework.jdbc.support.JdbcUtils;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import com.common.api.Constant;
import com.common.api.exception.CommonException;
import com.common.database.error.SqlExecuteError;
import com.common.util.BeanCopyUtil;

import lombok.extern.slf4j.Slf4j;

/**
 * @author ficus
 * 数据库工具适配器
 */
@Slf4j
public final class JdbcUtilsAdapter {

//	public static void execute(DataSource dataSource, String sql){
//        Connection conn = null;
//        Statement statement=null;
//        try {
//        	conn = dataSource.getConnection();
//        	statement=conn.createStatement();
//        	statement.execute(sql);
//        } catch (SQLException e) {
//			e.printStackTrace();
//			log.warn("数据源{},targetSqlInfo:{}执行异常", dataSource, sql);
//			throw new CommonException(SqlExecuteError.sqlExecuteError);
//		}finally {
//        	JdbcUtils.closeStatement(statement);
//        	JdbcUtils.closeConnection(conn);
//        }
//	}
	
	@SuppressWarnings("unchecked")
	public static <T> T selectOne(DataSource dataSource, String sql){
		List<Map<String, Object>> list=executeQuery(dataSource,sql);
		if(list==null||list.size()==0) {
			return null;
		}
		Map<String, Object> map=list.get(0);
		if(map.size()==0) {
			return null;
		}
		return (T)map.values().toArray()[0];
	}
	
	public static Map<String, Object> selectMap(DataSource dataSource, String sql){
		List<Map<String, Object>> list=executeQuery(dataSource,sql);
		if(list==null||list.size()==0) {
			return null;
		}
		Map<String, Object> map=list.get(0);
		return map;
	}
	
	public static <T> T selectObject(DataSource dataSource, String sql,Class<T> clazz) {
		List<Map<String, Object>> list=executeQuery(dataSource,sql);
		if(list==null||list.size()==0) {
			return null;
		}
		Map<String, Object> map=list.get(0);
		return BeanCopyUtil.map(map, clazz);
	}
	
	public static <T> List<T> selectList(DataSource dataSource, String sql,Class<T> clazz) {
		List<T> result=new ArrayList<>();
		List<Map<String, Object>> map = executeQuery(dataSource,sql);
		if(CollectionUtils.isEmpty(map)) {
			return result;
		}
		return BeanCopyUtil.mapList(map, clazz);
	}
	
    /**
     * @param dataSource
     * @param sql select * from table where id = ? and name = ?; 使用占位符?
     * @param parameters
     * @return
     */
    public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, Object... parameters) {
        return executeQuery(dataSource, sql, Arrays.asList(parameters));
    }

    /**
     * @param dataSource
     * @param sql select * from table where id = ? and name = ?; 使用占位符?
     * @param parameters
     * @return
     */
    public static List<Map<String, Object>> executeQuery(DataSource dataSource, String sql, List<Object> parameters) {
        Connection conn = null;
        try {
            conn = dataSource.getConnection();
            return executeQuery(conn, sql, parameters);
        }  catch (SQLException e) {
			e.printStackTrace();
			log.warn("数据源{},sql:{}执行异常;params:{}", dataSource, sql,parameters);
			throw new CommonException(SqlExecuteError.sqlExecuteError);
		}finally {
        	JdbcUtils.closeConnection(conn);
        }
    }

    private static List<Map<String, Object>> executeQuery(Connection conn, String sql, List<Object> parameters) throws SQLException {
        List<Map<String, Object>> rows = new ArrayList<Map<String, Object>>();
        PreparedStatement stmt = null;
        ResultSet rs = null;
        try {
            String[] sqls=sql.split(Constant.semicolon);
            boolean isBatch=false;
            if(!(sqls.length==1||(sql.endsWith(Constant.semicolon)&&sqls.length==2))) {
            	isBatch=true;
            	for (String string : sqls) {
					if(StringUtils.isEmpty(string.trim())) {
						continue ;
					}
		            stmt = conn.prepareStatement(string);
		            setParameters(stmt, parameters);
		            stmt.execute();
				}
            }else {
	            stmt = conn.prepareStatement(sql);
	            setParameters(stmt, parameters);
            }
            sql=sql.trim();
            sql=sql.toLowerCase();
            if(sql.startsWith("select")) {
            	rs = stmt.executeQuery();            	
            }else if(sql.startsWith("update")||sql.startsWith("insert")||sql.startsWith("delete")) {
            	Object result=0;
            	if(!isBatch) {
            		result = stmt.executeUpdate();  
            	}
            	Map<String, Object> map=new HashMap<>();
            	map.put("column1", result);
            	rows.add(map);
            	return rows; 
            }else {
            	// 没有返回结果
            	stmt.execute();
            	return rows;
            }
            ResultSetMetaData rsMeta = rs.getMetaData();
            while (rs.next()) {
                Map<String, Object> row = new LinkedHashMap<String, Object>();
                for (int i = 0, size = rsMeta.getColumnCount(); i < size; ++i) {
                    String columName = rsMeta.getColumnLabel(i + 1);
                    Object value = rs.getObject(i + 1);
                    if(columName==null||columName.trim().isEmpty()) {
                    	row.put("column"+(i+1), value);
                    }else {
                    	row.put(columName, value);
                    }
                }
                rows.add(row);
            }
        } finally {
        	JdbcUtils.closeResultSet(rs);
        	JdbcUtils.closeStatement(stmt);
        }
        return rows;
    }

    private static void setParameters(PreparedStatement stmt, List<Object> parameters) throws SQLException {
        for (int i = 0, size = parameters.size(); i < size; ++i) {
            Object param = parameters.get(i);
            stmt.setObject(i + 1, param);
        }
    }
}
